__all__ = ['Jumps']
from ..storage import *
class Jumps(Storage):

    SQL_DROP = ["""
        DROP TABLE IF EXISTS "jumps"
    """]

    # It is important for "source_system_id" to be the first field in PK, because it is used in join when augmenting "trips".
    # No searches are performed on "target_system_id", so we do not build index over it specially (where it is the first field).
    SQL_CREATE = ["""
        CREATE TABLE "jumps" (
            "source_system_id"      integer         not null REFERENCES "systems" ("system_id") MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
            "target_system_id"      integer         not null REFERENCES "systems" ("system_id") MATCH FULL ON DELETE RESTRICT ON UPDATE CASCADE,
            "security"              smallint        not null -- denormalized
        ) WITHOUT OIDS """,
        """ CREATE UNIQUE INDEX "jumps_source_to_target" ON "jumps" ("source_system_id", "target_system_id") """,
        """ CREATE UNIQUE INDEX "jumps_target_to_source" ON "jumps" ("target_system_id", "source_system_id") """,
    ]

    SQL_INSERT = ["""
        INSERT INTO "jumps" (
            "source_system_id",
            "target_system_id",
            "security"
        ) VALUES (
            %(source_system_id)s,
            %(target_system_id)s,
            (
                SELECT min("security")
                FROM "systems"
                WHERE "system_id" in (
                    %(source_system_id)s,
                    %(target_system_id)s)
            )
        )
    """]

    SQL_ANALYZE = ["""
        ANALYZE "jumps"
    """]

    def drop(self):
        self.execute(self.SQL_DROP)

    def create(self):
        self.execute(self.SQL_CREATE)

    def insert(self, rows):
        self.execute(self.SQL_INSERT, rows)

    def analyze(self):
        self.execute(self.SQL_ANALYZE)

    def adapt(self, fetcher):
        return\
            map(lambda row: {
                'source_system_id'  : row['fromSolarSystemID'   ],
                'target_system_id'  : row['toSolarSystemID'     ],
            },
            filter(lambda row: True,# nothing to filter, but just for being similar to others
            fetcher({
                'fromSolarSystemID' : int,
                'toSolarSystemID'   : int,
            }, 'mapSolarSystemJumps')))
